-- @owner: cr13
-- @date: 2025/3/27
-- @testpoint:同名参数数量不同的重载函数，删除并重建，部分合理报错
--step1: 创建函数，2个入参;expect:成功
drop FUNCTION if exists func_ddl0022(int,int);
CREATE FUNCTION func_ddl0022(a int, b int)
RETURNS int
AS $$
declare
sum int;
BEGIN
select a + b into sum;
return sum;
END;
$$
LANGUAGE plpgsql;
/
--step2: 创建函数，3个入参;expect:成功
drop FUNCTION if exists func_ddl0022(int,int,int);
CREATE FUNCTION func_ddl0022(a int, b int,c int)
RETURNS int
AS $$
declare
sum int;
BEGIN
select a + b + c into sum;
return sum;
END;
$$
LANGUAGE plpgsql;
/
--step3: 创建视图，引用3个入参的函数;expect:成功
drop view if exists v_ddl0022;
create view v_ddl0022 as select func_ddl0022(1,2,3);
--step4: 查询视图，成功
select * from v_ddl0022;
--step5: 删除视图引用的自定义函数;expect:成功
drop FUNCTION func_ddl0022(int,int,int);
--step6: 查询视图状态;expect:无效
select valid from pg_object where object_oid='v_ddl0022'::regclass;
--step7: 查询视图数据;expect:报错
select * from v_ddl0022;
--step8: 查询视图定义;expect:目前报错
select pg_get_viewdef('v_ddl0022');

--step9: 重建视图依赖的函数;expect:成功
CREATE FUNCTION func_ddl0022(a int, b int,c int)
RETURNS int
AS $$
declare
sum int;
BEGIN
select a + b + c into sum;
return sum;
END;
$$
LANGUAGE plpgsql;
/
--step10: 查询视图状态;expect:无效
select valid from pg_object where object_oid='v_ddl0022'::regclass;
--step11: 查询视图数据;expect:成功
select * from v_ddl0022;
--step12: 查询视图状态;expect:有效
select valid from pg_object where object_oid='v_ddl0022'::regclass;
--step13: 查询视图定义;expect:成功
select pg_get_viewdef('v_ddl0022');

--step14: 删除非视图依赖的函数;expect:成功
drop FUNCTION func_ddl0022(int,int);
--step15: 查询视图状态;expect:无效（此函数经历了一次无效到有效的过程
select valid from pg_object where object_oid='v_ddl0022'::regclass;
--step16: 查询视图数据;expect:成功
select * from v_ddl0022;
--step17: 查询视图定义;expect:成功
select pg_get_viewdef('v_ddl0022');
--step18: 清理环境;expect:成功
DROP VIEW IF EXISTS v_ddl0022;
DROP FUNCTION IF EXISTS func_ddl0022(int,int,int);
